home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Personal Computer World 2005 October
/
PCWOCT05.iso
/
Software
/
FromTheMag
/
XAMPP 1.4.14
/
xampp-win32-1.4.14-installer.exe
/
xampp
/
php
/
pear
/
DB
/
pgsql.php
< prev
next >
Wrap
PHP Script
|
2004-10-01
|
28KB
|
847 lines
<?php
/* vim: set expandtab tabstop=4 shiftwidth=4 foldmethod=marker: */
// +----------------------------------------------------------------------+
// | PHP Version 4 |
// +----------------------------------------------------------------------+
// | Copyright (c) 1997-2004 The PHP Group |
// +----------------------------------------------------------------------+
// | This source file is subject to version 2.02 of the PHP license, |
// | that is bundled with this package in the file LICENSE, and is |
// | available at through the world-wide-web at |
// | http://www.php.net/license/2_02.txt. |
// | If you did not receive a copy of the PHP license and are unable to |
// | obtain it through the world-wide-web, please send a note to |
// | license@php.net so we can mail you a copy immediately. |
// +----------------------------------------------------------------------+
// | Authors: Rui Hirokawa <hirokawa@php.net> |
// | Stig Bakken <ssb@php.net> |
// | Maintainer: Daniel Convissor <danielc@php.net> |
// +----------------------------------------------------------------------+
//
// $Id: pgsql.php,v 1.82 2004/09/25 21:58:00 danielc Exp $
require_once 'DB/common.php';
/**
* Database independent query interface definition for PHP's PostgreSQL
* extension.
*
* @package DB
* @version $Id: pgsql.php,v 1.82 2004/09/25 21:58:00 danielc Exp $
* @category Database
* @author Rui Hirokawa <hirokawa@php.net>
* @author Stig Bakken <ssb@php.net>
*/
class DB_pgsql extends DB_common
{
// {{{ properties
var $connection;
var $phptype, $dbsyntax;
var $prepare_tokens = array();
var $prepare_types = array();
var $transaction_opcount = 0;
var $dsn = array();
var $row = array();
var $num_rows = array();
var $affected = 0;
var $autocommit = true;
var $fetchmode = DB_FETCHMODE_ORDERED;
// }}}
// {{{ constructor
function DB_pgsql()
{
$this->DB_common();
$this->phptype = 'pgsql';
$this->dbsyntax = 'pgsql';
$this->features = array(
'prepare' => false,
'pconnect' => true,
'transactions' => true,
'limit' => 'alter'
);
$this->errorcode_map = array(
);
}
// }}}
// {{{ connect()
/**
* Connect to a database and log in as the specified user.
*
* @param $dsn the data source name (see DB::parseDSN for syntax)
* @param $persistent (optional) whether the connection should
* be persistent
*
* @return int DB_OK on success, a DB error code on failure.
*/
function connect($dsninfo, $persistent = false)
{
if (!DB::assertExtension('pgsql')) {
return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
}
$this->dsn = $dsninfo;
$protocol = $dsninfo['protocol'] ? $dsninfo['protocol'] : 'tcp';
$connstr = '';
if ($protocol == 'tcp') {
if ($dsninfo['hostspec']) {
$connstr .= 'host=' . $dsninfo['hostspec'];
}
if ($dsninfo['port']) {
$connstr .= ' port=' . $dsninfo['port'];
}
} elseif ($protocol == 'unix') {
// Allow for pg socket in non-standard locations.
if ($dsninfo['socket']) {
$connstr .= 'host=' . $dsninfo['socket'];
}
if ($dsninfo['port']) {
$connstr .= ' port=' . $dsninfo['port'];
}
}
if ($dsninfo['database']) {
$connstr .= ' dbname=\'' . addslashes($dsninfo['database']) . '\'';
}
if ($dsninfo['username']) {
$connstr .= ' user=\'' . addslashes($dsninfo['username']) . '\'';
}
if ($dsninfo['password']) {
$connstr .= ' password=\'' . addslashes($dsninfo['password']) . '\'';
}
if (!empty($dsninfo['options'])) {
$connstr .= ' options=' . $dsninfo['options'];
}
if (!empty($dsninfo['tty'])) {
$connstr .= ' tty=' . $dsninfo['tty'];
}
$connect_function = $persistent ? 'pg_pconnect' : 'pg_connect';
$ini = ini_get('track_errors');
if ($ini) {
$conn = @$connect_function($connstr);
} else {
ini_set('track_errors', 1);
$conn = @$connect_function($connstr);
ini_set('track_errors', $ini);
}
if ($conn == false) {
return $this->raiseError(DB_ERROR_CONNECT_FAILED, null,
null, null, strip_tags($php_errormsg));
}
$this->connection = $conn;
return DB_OK;
}
// }}}
// {{{ disconnect()
/**
* Log out and disconnect from the database.
*
* @return bool true on success, false if not connected.
*/
function disconnect()
{
$ret = @pg_close($this->connection);
$this->connection = null;
return $ret;
}
// }}}
// {{{ simpleQuery()
/**
* Send a query to PostgreSQL and return the results as a
* PostgreSQL resource identifier.
*
* @param $query the SQL query
*
* @return int returns a valid PostgreSQL result for successful SELECT
* queries, DB_OK for other successful queries. A DB error code
* is returned on failure.
*/
function simpleQuery($query)
{
$ismanip = DB::isManip($query);
$this->last_query = $query;
$query = $this->modifyQuery($query);
if (!$this->autocommit && $ismanip) {
if ($this->transaction_opcount == 0) {
$result = @pg_exec($this->connection, 'begin;');
if (!$result) {
return $this->pgsqlRaiseError();
}
}
$this->transaction_opcount++;
}
$result = @pg_exec($this->connection, $query);
if (!$result) {
return $this->pgsqlRaiseError();
}
// Determine which queries that should return data, and which
// should return an error code only.
if ($ismanip) {
$this->affected = @pg_cmdtuples($result);
return DB_OK;
} elseif (preg_match('/^\s*\(?\s*(SELECT(?!\s+INTO)|EXPLAIN|SHOW)\s/si', $query)) {
/* PostgreSQL commands:
ABORT, ALTER, BEGIN, CLOSE, CLUSTER, COMMIT, COPY,
CREATE, DECLARE, DELETE, DROP TABLE, EXPLAIN, FETCH,
GRANT, INSERT, LISTEN, LOAD, LOCK, MOVE, NOTIFY, RESET,
REVOKE, ROLLBACK, SELECT, SELECT INTO, SET, SHOW,
UNLISTEN, UPDATE, VACUUM
*/
$this->row[(int)$result] = 0; // reset the row counter.
$numrows = $this->numrows($result);
if (is_object($numrows)) {
return $numrows;
}
$this->num_rows[(int)$result] = $numrows;
$this->affected = 0;
return $result;
} else {
$this->affected = 0;
return DB_OK;
}
}
// }}}
// {{{ nextResult()
/**
* Move the internal pgsql result pointer to the next available result
*
* @param a valid fbsql result resource
*
* @access public
*
* @return true if a result is available otherwise return false
*/
function nextResult($result)
{
return false;
}
// }}}
// {{{ errorCode()
/**
* Determine PEAR::DB error code from the database's text error message.
*
* @param string $errormsg error message returned from the database
* @return integer an error number from a DB error constant
*/
function errorCode($errormsg)
{
static $error_regexps;
if (!isset($error_regexps)) {
$error_regexps = array(
'/(([Rr]elation|[Ss]equence|[Tt]able)( [\"\'].*[\"\'])? does not exist|[Cc]lass ".+" not found)$/' => DB_ERROR_NOSUCHTABLE,
'/[Cc]olumn [\"\'].*[\"\'] .*does not exist/' => DB_ERROR_NOSUCHFIELD,
'/[Rr]elation [\"\'].*[\"\'] already exists|[Cc]annot insert a duplicate key into (a )?unique index.*/' => DB_ERROR_ALREADY_EXISTS,
'/(divide|division) by zero$/' => DB_ERROR_DIVZERO,
'/pg_atoi: error in .*: can\'t parse /' => DB_ERROR_INVALID_NUMBER,
'/invalid input syntax for integer/' => DB_ERROR_INVALID_NUMBER,
'/ttribute [\"\'].*[\"\'] not found$|[Rr]elation [\"\'].*[\"\'] does not have attribute [\"\'].*[\"\']/' => DB_ERROR_NOSUCHFIELD,
'/parser: parse error at or near \"/' => DB_ERROR_SYNTAX,
'/syntax error at/' => DB_ERROR_SYNTAX,
'/violates not-null constraint/' => DB_ERROR_CONSTRAINT_NOT_NULL,
'/violates [\w ]+ constraint/' => DB_ERROR_CONSTRAINT,
'/referential integrity violation/' => DB_ERROR_CONSTRAINT
);
}
foreach ($error_regexps as $regexp => $code) {
if (preg_match($regexp, $errormsg)) {
return $code;
}
}
// Fall back to DB_ERROR if there was no mapping.
return DB_ERROR;
}
// }}}
// {{{ fetchInto()
/**
* Fetch a row and insert the data into an existing array.
*
* Formating of the array and the data therein are configurable.
* See DB_result::fetchInto() for more information.
*
* @param resource $result query result identifier
* @param array $arr (reference) array where data from the row
* should be placed
* @param int $fetchmode how the resulting array should be indexed
* @param int $rownum the row number to fetch
*
* @return mixed DB_OK on success, null when end of result set is
* reached or on failure
*
* @see DB_result::fetchInto()
* @access private
*/
function fetchInto($result, &$arr, $fetchmode, $rownum=null)
{
$result_int = (int)$result;
$rownum = ($rownum !== null) ? $rownum : $this->row[$result_int];
if ($rownum >= $this->num_rows[$result_int]) {
return null;
}
if ($fetchmode & DB_FETCHMODE_ASSOC) {
$arr = @pg_fetch_array($result, $rownum, PGSQL_ASSOC);
if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
$arr = array_change_key_case($arr, CASE_LOWER);
}
} else {
$arr = @pg_fetch_row($result, $rownum);
}
if (!$arr) {
$err = pg_errormessage($this->connection);
if (!$err) {
return null;
}
return $this->pgsqlRaiseError();
}
if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
$this->_rtrimArrayValues($arr);
}
if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
$this->_convertNullArrayValuesToEmpty($arr);
}
$this->row[$result_int] = ++$rownum;
return DB_OK;
}
// }}}
// {{{ freeResult()
/**
* Free the internal resources associated with $result.
*
* @param $result int PostgreSQL result identifier
*
* @return bool true on success, false if $result is invalid
*/
function freeResult($result)
{
if (is_resource($result)) {
unset($this->row[(int)$result]);
unset($this->num_rows[(int)$result]);
$this->affected = 0;
return @pg_freeresult($result);
}
return false;
}
// }}}
// {{{ quote()
/**
* @deprecated Deprecated in release 1.6.0
* @internal
*/
function quote($str) {
return $this->quoteSmart($str);
}
// }}}
// {{{ quoteSmart()
/**
* Format input so it can be safely used in a query
*
* @param mixed $in data to be quoted
*
* @return mixed Submitted variable's type = returned value:
* + null = the string <samp>NULL</samp>
* + boolean = string <samp>TRUE</samp> or <samp>FALSE</samp>
* + integer or double = the unquoted number
* + other (including strings and numeric strings) =
* the data escaped according to MySQL's settings
* then encapsulated between single quotes
*
* @internal
*/
function quoteSmart($in)
{
if (is_int($in) || is_double($in)) {
return $in;
} elseif (is_bool($in)) {
return $in ? 'TRUE' : 'FALSE';
} elseif (is_null($in)) {
return 'NULL';
} else {
return "'" . $this->escapeSimple($in) . "'";
}
}
// }}}
// {{{ escapeSimple()
/**
* Escape a string according to the current DBMS's standards
*
* PostgreSQL treats a backslash as an escape character, so they are
* removed.
*
* Not using pg_escape_string() yet because it requires PostgreSQL
* to be at version 7.2 or greater.
*
* @param string $str the string to be escaped
*
* @return string the escaped string
*
* @internal
*/
function escapeSimple($str) {
return str_replace("'", "''", str_replace('\\', '\\\\', $str));
}
// }}}
// {{{ numCols()
/**
* Get the number of columns in a result set.
*
* @param $result resource PostgreSQL result identifier
*
* @return int the number of columns per row in $result
*/
function numCols($result)
{
$cols = @pg_numfields($result);
if (!$cols) {
return $this->pgsqlRaiseError();
}
return $cols;
}
// }}}
// {{{ numRows()
/**
* Get the number of rows in a result set.
*
* @param $result resource PostgreSQL result identifier
*
* @return int the number of rows in $result
*/
function numRows($result)
{
$rows = @pg_numrows($result);
if ($rows === null) {
return $this->pgsqlRaiseError();
}
return $rows;
}
// }}}
// {{{ errorNative()
/**
* Get the native error code of the last error (if any) that
* occured on the current connection.
*
* @return int native PostgreSQL error code
*/
function errorNative()
{
return pg_errormessage($this->connection);
}
// }}}
// {{{ autoCommit()
/**
* Enable/disable automatic commits
*/
function autoCommit($onoff = false)
{
// XXX if $this->transaction_opcount > 0, we should probably
// issue a warning here.
$this->autocommit = $onoff ? true : false;
return DB_OK;
}
// }}}
// {{{ commit()
/**
* Commit the current transaction.
*/
function commit()
{
if ($this->transaction_opcount > 0) {
// (disabled) hack to shut up error messages from libpq.a
//@fclose(@fopen("php://stderr", "w"));
$result = @pg_exec($this->connection, 'end;');
$this->transaction_opcount = 0;
if (!$result) {
return $this->pgsqlRaiseError();
}
}
return DB_OK;
}
// }}}
// {{{ rollback()
/**
* Roll back (undo) the current transaction.
*/
function rollback()
{
if ($this->transaction_opcount > 0) {
$result = @pg_exec($this->connection, 'abort;');
$this->transaction_opcount = 0;
if (!$result) {
return $this->pgsqlRaiseError();
}
}
return DB_OK;
}
// }}}
// {{{ affectedRows()
/**
* Gets the number of rows affected by the last query.
* if the last query was a select, returns 0.
*
* @return int number of rows affected by the last query or DB_ERROR
*/
function affectedRows()
{
return $this->affected;
}
// }}}
// {{{ nextId()
/**
* Returns the next free id in a sequence
*
* @param string $seq_name name of the sequence
* @param boolean $ondemand when true, the seqence is automatically
* created if it does not exist
*
* @return int the next id number in the sequence. DB_Error if problem.
*
* @internal
* @see DB_common::nextID()
* @access public
*/
function nextId($seq_name, $ondemand = true)
{
$seqname = $this->getSequenceName($seq_name);
$repeat = false;
do {
$this->pushErrorHandling(PEAR_ERROR_RETURN);
$result =& $this->query("SELECT NEXTVAL('${seqname}')");
$this->popErrorHandling();
if ($ondemand && DB::isError($result) &&
$result->getCode() == DB_ERROR_NOSUCHTABLE) {
$repeat = true;
$this->pushErrorHandling(PEAR_ERROR_RETURN);
$result = $this->createSequence($seq_name);
$this->popErrorHandling();
if (DB::isError($result)) {
return $this->raiseError($result);
}
} else {
$repeat = false;
}
} while ($repeat);
if (DB::isError($result)) {
return $this->raiseError($result);
}
$arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
$result->free();
return $arr[0];
}
// }}}
// {{{ createSequence()
/**
* Create the sequence
*
* @param string $seq_name the name of the sequence
* @return mixed DB_OK on success or DB error on error
* @access public
*/
function createSequence($seq_name)
{
$seqname = $this->getSequenceName($seq_name);
$result = $this->query("CREATE SEQUENCE ${seqname}");
return $result;
}
// }}}
// {{{ dropSequence()
/**
* Drop a sequence
*
* @param string $seq_name the name of the sequence
* @return mixed DB_OK on success or DB error on error
* @access public
*/
function dropSequence($seq_name)
{
$seqname = $this->getSequenceName($seq_name);
return $this->query("DROP SEQUENCE ${seqname}");
}
// }}}
// {{{ modifyLimitQuery()
function modifyLimitQuery($query, $from, $count, $params = array())
{
$query = $query . " LIMIT $count OFFSET $from";
return $query;
}
// }}}
// {{{ pgsqlRaiseError()
/**
* Gather information about an error, then use that info to create a
* DB error object and finally return that object.
*
* @param integer $errno PEAR error number (usually a DB constant) if
* manually raising an error
* @return object DB error object
* @see errorNative()
* @see errorCode()
* @see DB_common::raiseError()
*/
function pgsqlRaiseError($errno = null)
{
$native = $this->errorNative();
if ($errno === null) {
$err = $this->errorCode($native);
} else {
$err = $errno;
}
return $this->raiseError($err, null, null, null, $native);
}
// }}}
// {{{ _pgFieldFlags()
/**
* Flags of a Field
*
* @param int $resource PostgreSQL result identifier
* @param int $num_field the field number
*
* @return string The flags of the field ("not_null", "default_value",
* "primary_key", "unique_key" and "multiple_key"
* are supported). The default value is passed
* through rawurlencode() in case there are spaces in it.
* @access private
*/
function _pgFieldFlags($resource, $num_field, $table_name)
{
$field_name = @pg_fieldname($resource, $num_field);
$result = @pg_exec($this->connection, "SELECT f.attnotnull, f.atthasdef
FROM pg_attribute f, pg_class tab, pg_type typ
WHERE tab.relname = typ.typname
AND typ.typrelid = f.attrelid
AND f.attname = '$field_name'
AND tab.relname = '$table_name'");
if (@pg_numrows($result) > 0) {
$row = @pg_fetch_row($result, 0);
$flags = ($row[0] == 't') ? 'not_null ' : '';
if ($row[1] == 't') {
$result = @pg_exec($this->connection, "SELECT a.adsrc
FROM pg_attribute f, pg_class tab, pg_type typ, pg_attrdef a
WHERE tab.relname = typ.typname AND typ.typrelid = f.attrelid
AND f.attrelid = a.adrelid AND f.attname = '$field_name'
AND tab.relname = '$table_name' AND f.attnum = a.adnum");
$row = @pg_fetch_row($result, 0);
$num = preg_replace("/'(.*)'::\w+/", "\\1", $row[0]);
$flags .= 'default_' . rawurlencode($num) . ' ';
}
} else {
$flags = '';
}
$result = @pg_exec($this->connection, "SELECT i.indisunique, i.indisprimary, i.indkey
FROM pg_attribute f, pg_class tab, pg_type typ, pg_index i
WHERE tab.relname = typ.typname
AND typ.typrelid = f.attrelid
AND f.attrelid = i.indrelid
AND f.attname = '$field_name'
AND tab.relname = '$table_name'");
$count = @pg_numrows($result);
for ($i = 0; $i < $count ; $i++) {
$row = @pg_fetch_row($result, $i);
$keys = explode(' ', $row[2]);
if (in_array($num_field + 1, $keys)) {
$flags .= ($row[0] == 't' && $row[1] == 'f') ? 'unique_key ' : '';
$flags .= ($row[1] == 't') ? 'primary_key ' : '';
if (count($keys) > 1)
$flags .= 'multiple_key ';
}
}
return trim($flags);
}
// }}}
// {{{ tableInfo()
/**
* Returns information about a table or a result set.
*
* NOTE: only supports 'table' and 'flags' if <var>$result</var>
* is a table name.
*
* @param object|string $result DB_result object from a query or a
* string containing the name of a table
* @param int $mode a valid tableInfo mode
* @return array an associative array with the information requested
* or an error object if something is wrong
* @access public
* @internal
* @see DB_common::tableInfo()
*/
function tableInfo($result, $mode = null)
{
if (isset($result->result)) {
/*
* Probably received a result object.
* Extract the result resource identifier.
*/
$id = $result->result;
$got_string = false;
} elseif (is_string($result)) {
/*
* Probably received a table name.
* Create a result resource identifier.
*/
$id = @pg_exec($this->connection, "SELECT * FROM $result LIMIT 0");
$got_string = true;
} else {
/*
* Probably received a result resource identifier.
* Copy it.
* Deprecated. Here for compatibility only.
*/
$id = $result;
$got_string = false;
}
if (!is_resource($id)) {
return $this->pgsqlRaiseError(DB_ERROR_NEED_MORE_DATA);
}
if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
$case_func = 'strtolower';
} else {
$case_func = 'strval';
}
$count = @pg_numfields($id);
// made this IF due to performance (one if is faster than $count if's)
if (!$mode) {
for ($i=0; $i<$count; $i++) {
$res[$i]['table'] = $got_string ? $case_func($result) : '';
$res[$i]['name'] = $case_func(@pg_fieldname($id, $i));
$res[$i]['type'] = @pg_fieldtype($id, $i);
$res[$i]['len'] = @pg_fieldsize($id, $i);
$res[$i]['flags'] = $got_string ? $this->_pgFieldflags($id, $i, $result) : '';
}
} else { // full
$res['num_fields']= $count;
for ($i=0; $i<$count; $i++) {
$res[$i]['table'] = $got_string ? $case_func($result) : '';
$res[$i]['name'] = $case_func(@pg_fieldname($id, $i));
$res[$i]['type'] = @pg_fieldtype($id, $i);
$res[$i]['len'] = @pg_fieldsize($id, $i);
$res[$i]['flags'] = $got_string ? $this->_pgFieldFlags($id, $i, $result) : '';
if ($mode & DB_TABLEINFO_ORDER) {
$res['order'][$res[$i]['name']] = $i;
}
if ($mode & DB_TABLEINFO_ORDERTABLE) {
$res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
}
}
}
// free the result only if we were called on a table
if ($got_string) {
@pg_freeresult($id);
}
return $res;
}
// }}}
// {{{ getTablesQuery()
/**
* Returns the query needed to get some backend info
* @param string $type What kind of info you want to retrieve
* @return string The SQL query string
*/
function getSpecialQuery($type)
{
switch ($type) {
case 'tables':
return "SELECT c.relname as \"Name\"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND c.relkind = 'r'
AND not exists (select 1 from pg_views where viewname = c.relname)
AND c.relname !~ '^(pg_|sql_)'
UNION
SELECT c.relname as \"Name\"
FROM pg_class c
WHERE c.relkind = 'r'
AND not exists (select 1 from pg_views where viewname = c.relname)
AND not exists (select 1 from pg_user where usesysid = c.relowner)
AND c.relname !~ '^pg_'";
case 'views':
// Table cols: viewname | viewowner | definition
return 'SELECT viewname FROM pg_views';
case 'users':
// cols: usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil
return 'SELECT usename FROM pg_user';
case 'databases':
return 'SELECT datname FROM pg_database';
case 'functions':
return 'SELECT proname FROM pg_proc';
default:
return null;
}
}
// }}}
}
/*
* Local variables:
* tab-width: 4
* c-basic-offset: 4
* End:
*/
?>